package br.com.vagacerta.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import br.com.vagacerta.modelo.Cupom;
import br.com.vagacerta.modelo.CupomAvulso;
import br.com.vagacerta.modelo.CupomMensalista;
import br.com.vagacerta.modelo.TipoCupom;

@Repository
public class CupomDAO {

	private Connection connection;
	private final VagaDAO vagaDAO;
	private final CancelaDAO cancelaDAO;
	private final TipoServicoDAO tipoServicoDAO;
	private final PagamentoDAO pagamentoDAO;

	@Autowired
	public CupomDAO(DataSource dataSource, VagaDAO vagaDAO,
			CancelaDAO cancelaDAO, TipoServicoDAO tipoServicoDAO, PagamentoDAO pagamentoDAO) {
		try {
			this.connection = dataSource.getConnection();
			this.vagaDAO = vagaDAO;
			this.cancelaDAO = cancelaDAO;
			this.tipoServicoDAO = tipoServicoDAO;
			this.pagamentoDAO = pagamentoDAO;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public CupomMensalista retornaCupomMensalistaPorIdCliente(long idCliente) {
		try {

			String sql = "SELECT CUP.*, CUPM.COD_CUPOM_MENSALISTA, CUPM.COD_CLIENTE, CUPM.VALIDADE, CUPM.VALOR_FINAL "
					+ "FROM CUPOM AS CUP INNER JOIN CUPOM_MENSALISTA AS CUPM ON "
					+ "CUP.COD_CUPOM = CUPM.COD_CUPOM "
					+ "WHERE COD_CLIENTE = ? "
					+ "ORDER BY CUP.DATA_EMISSAO DESC LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, idCliente);
			ResultSet rs = stmt.executeQuery();
			CupomMensalista cupomMensalista = new CupomMensalista();

			while (rs.next()) {

				cupomMensalista.setCodigoCupom(rs.getLong("COD_CUPOM"));
				cupomMensalista.setVaga(vagaDAO.retornaVagaPorId(rs
						.getLong("COD_VAGA")));
				cupomMensalista.setCancela(cancelaDAO.retornaCancelaPorId(rs
						.getLong("COD_CANCELA")));
				cupomMensalista.setCodigoCupomMensalista(rs
						.getLong("COD_CUPOM_MENSALISTA"));
				cupomMensalista.setTipoServico(tipoServicoDAO
						.retornaTipoServicoPorId(rs.getLong("COD_SERVICO")));
				cupomMensalista.setTipoCupom(retornaTipoCupomPorId(rs
						.getLong("COD_TIPO_CUPOM")));
				cupomMensalista.setNomeCupom(rs.getLong("NOME_CUPOM"));
				cupomMensalista.setStatusCupom(rs.getShort("STATUS_CUPOM"));

				Calendar dataE = Calendar.getInstance();
				dataE.setTime(rs.getTimestamp("DATA_EMISSAO"));

				cupomMensalista.setDataEmissaoCupom(dataE);

				cupomMensalista.setCodigoCupomMensalista(rs
						.getLong("COD_CUPOM_MENSALISTA"));
				
				boolean possuiPagamento = pagamentoDAO.existePagamentoPorCodigoCupom(cupomMensalista.getCodigoCupom());

				if (possuiPagamento) {

					Calendar dataV = Calendar.getInstance();
					dataV.setTime(rs.getTimestamp("VALIDADE"));
					cupomMensalista.setValidade(dataV);
					
					cupomMensalista.setValorFinal(rs.getDouble("VALOR_FINAL"));

				}

			}

			return cupomMensalista;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	private TipoCupom retornaTipoCupomPorId(long idTipoCupom) {

		try {

			String sql = "SELECT * FROM TIPO_CUPOM WHERE COD_TIPO_CUPOM = ? LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, idTipoCupom);
			ResultSet rs = stmt.executeQuery();
			TipoCupom tipoCupom = new TipoCupom();

			while (rs.next()) {

				tipoCupom.setCodigoTipoCupom(rs.getLong("COD_TIPO_CUPOM"));
				tipoCupom.setTipoCupom(rs.getString("TIPO_CUPOM"));

			}

			rs.close();
			stmt.close();

			return tipoCupom;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public void geraCupomClienteMensalista(long codigoTipoServico, long codigoCliente) throws SQLException {

		String sql = "INSERT INTO CUPOM (COD_VAGA, COD_SERVICO, COD_TIPO_CUPOM, NOME_CUPOM, STATUS_CUPOM, DATA_EMISSAO) VALUES ("
				+ "(SELECT V.COD_VAGA FROM VAGA AS V LEFT OUTER JOIN"
				+ " CUPOM AS C ON C.COD_VAGA = V.COD_VAGA"
				+ " WHERE V.COD_TIPO_VAGA = (SELECT COD_TIPO_VAGA FROM TIPO_VAGA"
				+ " WHERE DESCRICAO_TIPO_VAGA = 'CARRO_MENSALISTA')"
				+ " AND V.STATUS_VAGA = 0"
				+ " AND C.COD_VAGA IS NULL"
				+ " ORDER BY V.NIVEL_VAGA, V.NOME_VAGA LIMIT 1),"
				+ "	(SELECT COD_SERVICO FROM TIPO_SERVICO WHERE COD_SERVICO = ?),"
				+ " (SELECT COD_TIPO_CUPOM FROM TIPO_CUPOM WHERE TIPO_CUPOM = 'MENSALISTA'),"
				+ " ?, 0, SYSDATE())";

		PreparedStatement stmt;
		stmt = connection.prepareStatement(sql);

		Cupom cupom = new CupomMensalista();
		cupom.setNomeCupom(cupom.geraNomeCupom(verificaUltimoCupom()));
		long nomeCupom = cupom.getNomeCupom();

		stmt.setLong(1, codigoTipoServico);
		stmt.setLong(2, nomeCupom);

		stmt.execute();
		stmt.close();

		String sql2 = "SELECT COD_CUPOM FROM CUPOM ORDER BY COD_CUPOM DESC LIMIT 1";

		PreparedStatement stmt2;
		stmt2 = connection.prepareStatement(sql2);
		ResultSet rs = stmt2.executeQuery();
		long tempoCodCupom = 0;
		while (rs.next()) {
			tempoCodCupom = rs.getLong("COD_CUPOM");
		}

		rs.close();
		stmt2.close();

		String sql3 = "INSERT INTO CUPOM_MENSALISTA (COD_CUPOM, COD_CLIENTE) VALUES (?, ?)";
		PreparedStatement stmt3;
		stmt3 = connection.prepareStatement(sql3);

		stmt3.setLong(1, tempoCodCupom);
		stmt3.setLong(2, codigoCliente);

		stmt3.execute();
		stmt3.close();

	}

	public CupomAvulso geraCupomClienteAvulso(long codigoCancela, Calendar dataEntrada) {
		try {
			String sql = "INSERT INTO CUPOM (COD_VAGA, COD_CANCELA, COD_SERVICO, COD_TIPO_CUPOM, NOME_CUPOM, "
					+ "STATUS_CUPOM, DATA_EMISSAO) VALUES ((SELECT COD_VAGA FROM VAGA"
					+ " WHERE COD_TIPO_VAGA = (SELECT COD_TIPO_VAGA FROM TIPO_VAGA"
					+ " WHERE DESCRICAO_TIPO_VAGA = 'CARRO_AVULSO')"
					+ " AND STATUS_VAGA = 0"
					+ " ORDER BY NIVEL_VAGA, NOME_VAGA LIMIT 1),"
					+ "?,"
					+ " (SELECT COD_SERVICO FROM TIPO_SERVICO WHERE TIPO_SERVICO = 'HORA'),"
					+ " (SELECT COD_TIPO_CUPOM FROM TIPO_CUPOM WHERE TIPO_CUPOM = 'AVULSO'),"
					+ " ?, 1, ?)";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			Cupom cupom = new CupomAvulso();

			cupom.setNomeCupom(cupom.geraNomeCupom(verificaUltimoCupom()));
			long nomeCupom = cupom.getNomeCupom();

			stmt.setLong(1, codigoCancela);
			stmt.setLong(2, nomeCupom);
			stmt.setTimestamp(3,  new Timestamp(dataEntrada.getTimeInMillis()));

			stmt.execute();
			stmt.close();

			String sql2 = "SELECT COD_CUPOM FROM CUPOM ORDER BY COD_CUPOM DESC LIMIT 1";

			PreparedStatement stmt2;
			stmt2 = connection.prepareStatement(sql2);
			ResultSet rs = stmt2.executeQuery();
			long tempoCodCupom = 0;
			while (rs.next()) {
				tempoCodCupom = rs.getLong("COD_CUPOM");
			}

			rs.close();
			stmt2.close();

			String sql3 = "INSERT INTO CUPOM_AVULSO (COD_CUPOM) VALUES (?)";
			PreparedStatement stmt3;
			stmt3 = connection.prepareStatement(sql3);

			stmt3.setLong(1, tempoCodCupom);

			stmt3.execute();
			stmt3.close();

			return (CupomAvulso) cupom;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public String verificaUltimoCupom() {

		try {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
			Date date = new Date();
			String dataTemp = sdf.format(date);
			dataTemp = dataTemp + "%";

			String sql = "SELECT NOME_CUPOM FROM CUPOM WHERE NOME_CUPOM LIKE ? ORDER BY NOME_CUPOM DESC LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setString(1, dataTemp);

			ResultSet rs = stmt.executeQuery();
			String ultimoCupom = null;

			while (rs.next()) {
				ultimoCupom = String.valueOf(rs.getLong("NOME_CUPOM"));
			}

			rs.close();
			stmt.close();

			return ultimoCupom;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public boolean existeCupomEmitidoParaCliente(long idCliente) {
		try {

			String sql = "SELECT IF((SELECT count(*) FROM CUPOM_MENSALISTA WHERE COD_CLIENTE = ?) = 1,'true','false') AS 'EXISTE_CUPOM_CLIENTE'";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, idCliente);

			ResultSet rs = stmt.executeQuery();
			boolean existeCupom = false;

			while (rs.next()) {
				existeCupom = Boolean.valueOf(rs
						.getString("EXISTE_CUPOM_CLIENTE"));
			}

			rs.close();
			stmt.close();

			return existeCupom;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public boolean existeCupomEmitidoPeloNome(long nomeCupom) {
		try {

			String sql = "SELECT IF((SELECT count(*) FROM CUPOM WHERE NOME_CUPOM = ?) = 1,'true','false') AS 'EXISTE_CUPOM'";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);

			ResultSet rs = stmt.executeQuery();
			boolean existeCupom = false;

			while (rs.next()) {
				existeCupom = Boolean.valueOf(rs.getString("EXISTE_CUPOM"));
			}

			rs.close();
			stmt.close();

			return existeCupom;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	public boolean existeCupomMensalistaEmitidoPeloNome(long nomeCupom) {
		try {

			String sql = "SELECT IF((SELECT count(*) FROM CUPOM WHERE NOME_CUPOM = ? AND COD_TIPO_CUPOM = 2) = 1,'true','false') AS 'EXISTE_CUPOM'";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);

			ResultSet rs = stmt.executeQuery();
			boolean existeCupom = false;

			while (rs.next()) {
				existeCupom = Boolean.valueOf(rs.getString("EXISTE_CUPOM"));
			}

			rs.close();
			stmt.close();

			return existeCupom;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public boolean verificaStatusCupomPeloNome(long nomeCupom) {
		try {
			String sql = "SELECT IF((SELECT STATUS_CUPOM FROM CUPOM WHERE NOME_CUPOM = ?) = 1,'true','false') AS 'STATUS_CUPOM'";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);

			ResultSet rs = stmt.executeQuery();
			boolean statusCupom = false;

			while (rs.next()) {
				statusCupom = Boolean.valueOf(rs.getString("STATUS_CUPOM"));
			}

			rs.close();
			stmt.close();

			return statusCupom;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void alteraCupomMensalistaParaEntradaCancela(long nomeCupom,
			long codigoCancela) {
		try {

			String sql = "UPDATE CUPOM SET STATUS_CUPOM = 1, COD_CANCELA = ? WHERE NOME_CUPOM = ?";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, codigoCancela);
			stmt.setLong(2, nomeCupom);

			stmt.execute();
			stmt.close();

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public String retornaVagaPeloNomeCupom(long nomeCupom) {
		try {
			String sql = "SELECT V.NOME_VAGA FROM VAGA AS V INNER JOIN CUPOM AS C ON C.COD_VAGA  = V.COD_VAGA WHERE NOME_CUPOM = ?";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);

			ResultSet rs = stmt.executeQuery();
			String nomeVaga = null;

			while (rs.next()) {
				nomeVaga = rs.getString("NOME_VAGA");
			}

			rs.close();
			stmt.close();

			return nomeVaga;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		
	}
	
	
	public TipoCupom retornaTipoCupomPorNomeCupom(long nomeCupom) {

		try {

			String sql = "SELECT TC.* FROM TIPO_CUPOM AS TC INNER JOIN CUPOM AS C ON C.COD_TIPO_CUPOM = TC.COD_TIPO_CUPOM WHERE C.NOME_CUPOM = ?";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);
			ResultSet rs = stmt.executeQuery();
			TipoCupom tipoCupom = new TipoCupom();

			while (rs.next()) {

				tipoCupom.setCodigoTipoCupom(rs.getLong("COD_TIPO_CUPOM"));
				tipoCupom.setTipoCupom(rs.getString("TIPO_CUPOM"));

			}

			rs.close();
			stmt.close();

			return tipoCupom;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}
	
	
	public CupomMensalista retornaCupomMensalistaPeloNomeCupom(long nomeCupom) {
		try {

			String sql = "SELECT CUP.*, CUPM.COD_CUPOM_MENSALISTA, CUPM.COD_CLIENTE, CUPM.VALIDADE, CUPM.VALOR_FINAL "
					+ "FROM CUPOM AS CUP INNER JOIN CUPOM_MENSALISTA AS CUPM ON "
					+ "CUP.COD_CUPOM = CUPM.COD_CUPOM "
					+ "WHERE CUP.NOME_CUPOM = ? "
					+ "ORDER BY CUP.DATA_EMISSAO DESC LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);
			ResultSet rs = stmt.executeQuery();
			CupomMensalista cupomMensalista = new CupomMensalista();

			while (rs.next()) {

				cupomMensalista.setCodigoCupom(rs.getLong("COD_CUPOM"));
				cupomMensalista.setVaga(vagaDAO.retornaVagaPorId(rs
						.getLong("COD_VAGA")));
				cupomMensalista.setCancela(cancelaDAO.retornaCancelaPorId(rs
						.getLong("COD_CANCELA")));
				cupomMensalista.setTipoServico(tipoServicoDAO
						.retornaTipoServicoPorId(rs.getLong("COD_SERVICO")));
				cupomMensalista.setTipoCupom(retornaTipoCupomPorId(rs
						.getLong("COD_TIPO_CUPOM")));
				cupomMensalista.setNomeCupom(rs.getLong("NOME_CUPOM"));
				cupomMensalista.setStatusCupom(rs.getShort("STATUS_CUPOM"));

				Calendar dataE = Calendar.getInstance();
				dataE.setTime(rs.getTimestamp("DATA_EMISSAO"));

				cupomMensalista.setDataEmissaoCupom(dataE);

				cupomMensalista.setCodigoCupomMensalista(rs
						.getLong("COD_CUPOM_MENSALISTA"));
				
				boolean possuiPagamento = pagamentoDAO.existePagamentoPorCodigoCupom(cupomMensalista.getCodigoCupom());

				if (possuiPagamento) {

					Calendar dataV = Calendar.getInstance();
					dataV.setTime(rs.getTimestamp("VALIDADE"));
					cupomMensalista.setValidade(dataV);
					
					cupomMensalista.setValorFinal(rs.getDouble("VALOR_FINAL"));

				}

			}

			return cupomMensalista;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

	}

	public CupomAvulso retornaCupomAvulsoPeloNomeCupom(long nomeCupom) {
		try {

			String sql = "SELECT CUP.*, CUPA.COD_CUPOM_AVULSO, CUPA.HORA_SAIDA, CUPA.VALOR_FINAL"
					+ " FROM CUPOM AS CUP INNER JOIN CUPOM_AVULSO AS CUPA ON" 
					+ " CUP.COD_CUPOM = CUPA.COD_CUPOM"
					+ " WHERE CUP.NOME_CUPOM = ?" 
					+ " ORDER BY CUP.DATA_EMISSAO DESC LIMIT 1";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, nomeCupom);
			ResultSet rs = stmt.executeQuery();
			CupomAvulso cupomAvulso = new CupomAvulso();

			while (rs.next()) {

				cupomAvulso.setCodigoCupom(rs.getLong("COD_CUPOM"));
				cupomAvulso.setVaga(vagaDAO.retornaVagaPorId(rs
						.getLong("COD_VAGA")));
				cupomAvulso.setCancela(cancelaDAO.retornaCancelaPorId(rs
						.getLong("COD_CANCELA")));
				cupomAvulso.setTipoServico(tipoServicoDAO
						.retornaTipoServicoPorId(rs.getLong("COD_SERVICO")));
				cupomAvulso.setTipoCupom(retornaTipoCupomPorId(rs
						.getLong("COD_TIPO_CUPOM")));
				cupomAvulso.setNomeCupom(rs.getLong("NOME_CUPOM"));
				cupomAvulso.setStatusCupom(rs.getShort("STATUS_CUPOM"));

				Calendar dataE = Calendar.getInstance();
				dataE.setTime(rs.getTimestamp("DATA_EMISSAO"));

				cupomAvulso.setDataEmissaoCupom(dataE);

				cupomAvulso.setCodigoCupomAvulso(rs
						.getLong("COD_CUPOM_AVULSO"));
				
				boolean possuiPagamento = pagamentoDAO.existePagamentoPorCodigoCupom(cupomAvulso.getCodigoCupom());

				if (possuiPagamento) {

					Calendar dataV = Calendar.getInstance();
					dataV.setTime(rs.getTimestamp("HORA_SAIDA"));
					cupomAvulso.setHoraSaida(dataV);
					
					cupomAvulso.setValorFinal(rs.getDouble("VALOR_FINAL"));

				}

			}

			return cupomAvulso;

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public void insereDadosPagamentoCupomMensalista(CupomMensalista cupomMensalista) throws SQLException{


			String sql = "UPDATE CUPOM_MENSALISTA  SET VALIDADE ="
						+ " (SELECT"
						+ " 	CASE"
						+ "         WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'MENSAL' THEN (SELECT adddate(sysdate(), INTERVAL 1 MONTH))"

						+ " 		WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'TRIMESTRAL' THEN (SELECT adddate(sysdate(), INTERVAL 3 MONTH))"

						+ " 		WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'SEMESTRAL' THEN (SELECT adddate(sysdate(), INTERVAL 6 MONTH))"

						+ " 		WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'ANUAL' THEN (SELECT adddate(sysdate(), INTERVAL 1 YEAR))"

						+ " 		ELSE NULL"
						+ " END AS DATA),"
						+ "  VALOR_FINAL = (SELECT"
						+ " 	CASE"
						+ "         WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'MENSAL' THEN (SELECT VALOR FROM TIPO_SERVICO WHERE TIPO_SERVICO = 'MENSAL')"
						+ " 		WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'TRIMESTRAL' THEN (SELECT VALOR FROM TIPO_SERVICO WHERE TIPO_SERVICO = 'TRIMESTRAL')"
						+ " 		WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'SEMESTRAL' THEN (SELECT VALOR FROM TIPO_SERVICO WHERE TIPO_SERVICO = 'SEMESTRAL')"
						+ " 		WHEN (SELECT SERV.TIPO_SERVICO FROM TIPO_SERVICO SERV INNER join"
						+ " 				CUPOM CUP ON SERV.COD_SERVICO = CUP.COD_SERVICO"
						+ " 				WHERE CUP.COD_CUPOM = ?) = 'ANUAL' THEN (SELECT VALOR FROM TIPO_SERVICO WHERE TIPO_SERVICO = 'ANUAL')"
						+ " 		ELSE NULL"
						+ " END AS VALOR) "
						+ " WHERE COD_CUPOM = ?";

			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);

			stmt.setLong(1, cupomMensalista.getCodigoCupom());
			stmt.setLong(2, cupomMensalista.getCodigoCupom());
			stmt.setLong(3, cupomMensalista.getCodigoCupom());
			stmt.setLong(4, cupomMensalista.getCodigoCupom());
			stmt.setLong(5, cupomMensalista.getCodigoCupom());
			stmt.setLong(6, cupomMensalista.getCodigoCupom());
			stmt.setLong(7, cupomMensalista.getCodigoCupom());
			stmt.setLong(8, cupomMensalista.getCodigoCupom());
			stmt.setLong(9, cupomMensalista.getCodigoCupom());

			stmt.execute();
			stmt.close();

			
	}

	public void insereDadosPagamentoCupomAvulso(Long codigoCupom, double valorPago) throws SQLException{

		String sql = "UPDATE CUPOM_AVULSO SET HORA_SAIDA = SYSDATE(), VALOR_FINAL = ? WHERE COD_CUPOM = ?";
		
		PreparedStatement stmt;
		stmt = connection.prepareStatement(sql);

		stmt.setDouble(1, valorPago);
		stmt.setLong(2, codigoCupom);

		stmt.execute();
		stmt.close();

		
	}

	public List<Cupom> retornaListaCuponsPorData(Calendar dateInicial,	Calendar dateFinal) {
		try {
			
			String sql = "SELECT CUP.*, CA.COD_CUPOM_AVULSO, CA.HORA_SAIDA, CA.VALOR_FINAL AS 'VALOR_FINAL_AVULSO', CM.COD_CUPOM_MENSALISTA, CM.VALIDADE, CM.VALOR_FINAL AS 'VALOR_FINAL_MENSALISTA'"
						+ " FROM CUPOM AS CUP LEFT JOIN CUPOM_AVULSO AS CA"
						+ " ON CA.COD_CUPOM = CUP.COD_CUPOM"
						+ " LEFT JOIN CUPOM_MENSALISTA AS CM"
						+ " ON CM.COD_CUPOM = CUP.COD_CUPOM"
						+ " WHERE CUP.DATA_EMISSAO BETWEEN ? AND ?";
			
			
			PreparedStatement stmt;
			stmt = connection.prepareStatement(sql);
			
			
			long timeInicial = dateInicial.getTimeInMillis();
			java.sql.Timestamp tsi = new java.sql.Timestamp(timeInicial);
			
			long timeFinal = dateFinal.getTimeInMillis();
			java.sql.Timestamp tsf = new java.sql.Timestamp(timeFinal);
			
			stmt.setTimestamp(1, tsi);
			stmt.setTimestamp(2, tsf);

			ResultSet rs = stmt.executeQuery();
			List<Cupom> listaCupom = new ArrayList<Cupom>();
			while (rs.next()) {

				if(rs.getLong("COD_TIPO_CUPOM") == 1) {
					
					CupomAvulso cupom = new CupomAvulso();

					cupom.setCodigoCupom(rs.getLong("COD_CUPOM"));
					cupom.setVaga(vagaDAO.retornaVagaPorId(rs
							.getLong("COD_VAGA")));
					cupom.setCancela(cancelaDAO.retornaCancelaPorId(rs
							.getLong("COD_CANCELA")));
					cupom.setTipoServico(tipoServicoDAO
							.retornaTipoServicoPorId(rs.getLong("COD_SERVICO")));
					cupom.setTipoCupom(retornaTipoCupomPorId(rs
							.getLong("COD_TIPO_CUPOM")));
					cupom.setNomeCupom(rs.getLong("NOME_CUPOM"));
					cupom.setStatusCupom(rs.getShort("STATUS_CUPOM"));
					
					Calendar dataE = Calendar.getInstance();
					dataE.setTime(rs.getTimestamp("DATA_EMISSAO"));

					cupom.setDataEmissaoCupom(dataE);

					cupom.setCodigoCupomAvulso(rs
							.getLong("COD_CUPOM_AVULSO"));
					
					cupom.setCodigoCupomAvulso(rs
							.getLong("COD_CUPOM_AVULSO"));
					
					boolean possuiPagamento = pagamentoDAO.existePagamentoPorCodigoCupom(cupom.getCodigoCupom());

					if (possuiPagamento) {

						Calendar dataV = Calendar.getInstance();
						dataV.setTime(rs.getTimestamp("HORA_SAIDA"));
						cupom.setHoraSaida(dataV);
						
						cupom.setValorFinal(rs.getDouble("VALOR_FINAL_AVULSO"));

					}
					
					listaCupom.add(cupom);
					
					
				} else if (rs.getLong("COD_TIPO_CUPOM") == 2) {
				
					CupomMensalista cupom = new CupomMensalista();
				
					cupom.setCodigoCupom(rs.getLong("COD_CUPOM"));
					cupom.setVaga(vagaDAO.retornaVagaPorId(rs
							.getLong("COD_VAGA")));
					cupom.setCancela(cancelaDAO.retornaCancelaPorId(rs
							.getLong("COD_CANCELA")));
					cupom.setTipoServico(tipoServicoDAO
							.retornaTipoServicoPorId(rs.getLong("COD_SERVICO")));
					cupom.setTipoCupom(retornaTipoCupomPorId(rs
							.getLong("COD_TIPO_CUPOM")));
					cupom.setNomeCupom(rs.getLong("NOME_CUPOM"));
					cupom.setStatusCupom(rs.getShort("STATUS_CUPOM"));
					
					Calendar dataE = Calendar.getInstance();
					dataE.setTime(rs.getTimestamp("DATA_EMISSAO"));

					cupom.setDataEmissaoCupom(dataE);
					
					boolean possuiPagamento = pagamentoDAO.existePagamentoPorCodigoCupom(cupom.getCodigoCupom());

					if (possuiPagamento) {

						Calendar dataV = Calendar.getInstance();
						dataV.setTime(rs.getTimestamp("VALIDADE"));
						cupom.setValidade(dataV);
						
						cupom.setValorFinal(rs.getDouble("VALOR_FINAL_MENSALISTA"));

					}
					
					listaCupom.add(cupom);
					
				}
				

			}
			
			rs.close();
			stmt.close();

			return listaCupom;
			
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		
		
		
		
	}


	


}
